package dao.server;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

public class DAO {
	private String username;
	private String password;
	private int port;
	private Connection con;

	public DAO() {
		this.username = "";
		this.password = "";
	}

	public DAO(String username, String password, int port) {
		this.username = username;
		this.password = password;
		this.port = port;
	}

	public int getPort() {
		return port;
	}

	public void setPort(int port) {
		this.port = port;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public boolean getConnection() {
		try {
			Class.forName("com.mysql.jdbc.Driver");
		} catch (ClassNotFoundException cnfe) {
			System.out.println("Error loading driver: " + cnfe);
		}
		try {
			String url = "jdbc:mysql://localhost:" + port + "/db_testing?characterEncoding=utf8";

			con = DriverManager.getConnection(url, username, password);

			return true;
		} catch (Exception e) {
			// e.printStackTrace();
			return false;
		}
	}

	public void closeConnection() {
		try {
			if (!con.isClosed()) {
				con.close();
			}
		} catch (Exception e) {
			// e.printStackTrace();
		}
	}

	// user
	public User getUser(User user) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_user WHERE username = '%s'",
					user.getUsername());
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				if (!resultSet.getString("username").equals("")) {
					user.setId(resultSet.getInt("id"));
					user.setUsername(resultSet.getString("username"));
					user.setPassword(resultSet.getString("password"));
					return user;
				} else
					return null;
			}
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	public boolean checkLogin(User user) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("SELECT * FROM tbl_user WHERE username = '%s' AND password = '%s' AND status != 'login'",
							user.getUsername(), user.getPassword());
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				if (!resultSet.getString("username").equals("")) {
					user.setId(resultSet.getInt("id"));
					user.setUsername(resultSet.getString("username"));
					user.setPassword(resultSet.getString("password"));
					return true;
				}					
			}
			return false;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}		
	}

	public void login(User user) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_user SET lastlogin = NOW(),status = 'login' WHERE id = %d",
							user.getId());
			statement.executeUpdate(query);
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
	}

	public void logout(User user) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_user SET status = 'logout' WHERE username = '%s'",
							user.getUsername());
			statement.executeUpdate(query);
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
	}

	public boolean addUser(User user) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("INSERT INTO tbl_user(username,password,lastlogin,status) VALUES('%s','%s',NOW(),'')",
							user.getUsername(), user.getPassword());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteUser(User user) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_user WHERE username = '%s'",
					user.getUsername());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public void serverDown() {
		try {
			ArrayList<User> listUser = new ArrayList<User>();
			Statement statement = con.createStatement();
			String query = String
					.format("SELECT * FROM tbl_user WHERE status = 'login'");
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				User user = new User();
				user.setId(resultSet.getInt("id"));
				listUser.add(user);
			}
			for (User user : listUser) {
				query = String
						.format("UPDATE tbl_user SET status = 'logout' WHERE id = '%d'",
								user.getId());
				statement.executeUpdate(query);
			}
		} catch (Exception e) {
			System.err.println("Error with connection: " + e);
		}
	}

	// project
	public boolean addProject(Project project) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"INSERT INTO tbl_project(name,time) VALUES('%s',NOW())",
					project.getName());
			statement.executeUpdate(query);
			query = String.format("SELECT MAX(id) as max FROM tbl_project");
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				project.setId(resultSet.getInt("max"));
			}
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean updateProject(Project project) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_project SET name = '%s',time = NOW() WHERE id = %d",
							project.getName(), project.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteProject(Project project) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_project WHERE id = %d", project.getId());
			statement.executeUpdate(query);
			deleteFunction(project.getId());
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public Project getProject(int id) {
		Project project = new Project("");
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_project WHERE id = %d", id);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				project.setId(resultSet.getInt("id"));
				project.setName(resultSet.getString("name"));
				project.setTime(resultSet.getString("time"));
			}
			return project;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return null;
		}
	}

	public ArrayList<Project> getAllProject() {
		ArrayList<Project> listProject = new ArrayList<Project>();
		try {
			Statement statement = con.createStatement();
			String query = String.format("SELECT * FROM tbl_project");
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				Project project = new Project();
				project.setId(resultSet.getInt("id"));
				project.setName(resultSet.getString("name"));
				project.setTime(resultSet.getString("time"));
				listProject.add(project);
			}
			return listProject;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	// test module
	public boolean addTestModule(TestModule tm) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("INSERT INTO tbl_testmodule(id_project,name,time,description) VALUES(%d,'%s',NOW(),'%s')",
							tm.getIdProject(), tm.getName(), tm.getTime(),
							tm.getDescription());
			statement.executeUpdate(query);
			query = String.format("SELECT MAX(id) as max FROM tbl_testmodule");
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				tm.setId(resultSet.getInt("max"));
			}
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean updateTestModule(TestModule tm) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testmodule SET id_project = %d,name = '%s',time = NOW(),description = '%s' WHERE id = %d",
							tm.getIdProject(), tm.getName(),
							tm.getDescription(), tm.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean renameTestModule(TestModule tm) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"UPDATE tbl_testmodule SET name = '%s' WHERE id = %d",
					tm.getName(), tm.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteTestModule(TestModule tm) {
		try {
			//delete all test case in test module
			for(TestCase tc : getAllTestCaseInTestModule(tm.getId()))
				deleteTestCase(tc);
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_testmodule WHERE id = %d", tm.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public TestModule getTestModule(int id) {
		TestModule tm = new TestModule();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testmodule WHERE id = %d", id);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				tm.setId(resultSet.getInt("id"));
				tm.setIdProject(resultSet.getInt("id_project"));
				tm.setName(resultSet.getString("name"));
				tm.setTime(resultSet.getString("time"));
				tm.setDescription(resultSet.getString("description"));
			}
			return tm;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	public ArrayList<TestModule> getAllTestModule(int idProject) {
		ArrayList<TestModule> listTestModule = new ArrayList<TestModule>();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testmodule WHERE id_project = %d",
					idProject);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestModule tm = new TestModule();
				tm.setId(resultSet.getInt("id"));
				tm.setIdProject(resultSet.getInt("id_project"));
				tm.setName(resultSet.getString("name"));
				tm.setTime(resultSet.getString("time"));
				tm.setDescription(resultSet.getString("description"));
				listTestModule.add(tm);
			}
			return listTestModule;
		} catch (Exception e) {
			System.err.println("Error with connection: " + e);
		}
		return null;
	}

	// test suite
	public boolean addTestSuite(TestSuite ts) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("INSERT INTO tbl_testsuite(id_project,name,time,description) VALUES(%d,'%s',NOW(),'%s')",
							ts.getIdProject(), ts.getName(), ts.getTime(),
							ts.getDescription());
			statement.executeUpdate(query);
			query = String.format("SELECT MAX(id) as max FROM tbl_testsuite");
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				ts.setId(resultSet.getInt("max"));
			}
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean updateTestSuite(TestSuite ts) {

		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testsuite SET id_project = %d,name = '%s',time = NOW(),description = '%s',code = code + 1 WHERE id = %d",
							ts.getIdProject(), ts.getName(),
							ts.getDescription(), ts.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}
	
	public boolean checkOutTestSuite(TestSuite ts) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testsuite SET checkout = '%s' WHERE id = %d",
							ts.getCheckOut(), ts.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}
	
	public void releaseAllCheckOutTestSuite(String checkOut) {
		try {
			ArrayList<TestSuite> listTestSuite = this.getAllTestSuite(checkOut);
			if(listTestSuite != null){
				Statement statement = con.createStatement();
				for (TestSuite ts : listTestSuite) {
					String query = String
							.format("UPDATE tbl_testsuite SET checkout = '' WHERE id = %d",
									ts.getId());
					statement.executeUpdate(query);
				}
			}
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
	}

	public boolean updateTestCaseInTestSuite(TestSuite ts) {
		try {
			deleteAllTestCaseInTestSuite(ts); // remove all test case in test suite
			Statement statement = con.createStatement();
			for (int i = 0; i < ts.getListTestCase().size(); i++) {
				TestCase tc = ts.getListTestCase().get(i);
				String query = String
						.format("INSERT INTO tbl_run(id_testsuite,id_testcase,tbl_run.order) VALUES(%d,%d,%d)",
								ts.getId(), tc.getId(), tc.getOrder());
				statement.executeUpdate(query);
			}
			return true;
		} catch (Exception e) {
			System.err.println("Error with connection: " + e);
			return false;
		}
	}
	
	private void deleteAllTestCaseInTestSuite(TestSuite ts){
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_run WHERE id_testsuite = %d", ts.getId());
			statement.executeUpdate(query);			
		} catch (Exception e) {
			//System.err.println("Error with connection: " + e);			
		}
	}

	public boolean renameTestSuite(TestSuite ts) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"UPDATE tbl_testsuite SET name = '%s' WHERE id = %d",
					ts.getName(), ts.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteTestSuite(TestSuite ts) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_testsuite WHERE id = %d", ts.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public TestSuite getTestSuite(int id) {
		TestSuite ts = new TestSuite();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testsuite WHERE id = %d", id);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				ts.setId(resultSet.getInt("id"));
				ts.setIdProject(resultSet.getInt("id_project"));
				ts.setName(resultSet.getString("name"));
				ts.setTime(resultSet.getString("time"));
				ts.setDescription(resultSet.getString("description"));
				ts.setCode(resultSet.getInt("code"));
				ts.setCheckOut(resultSet.getString("checkout"));
			}
			return ts;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	public ArrayList<TestSuite> getAllTestSuite(int idProject) {
		ArrayList<TestSuite> listTestSuite = new ArrayList<TestSuite>();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testsuite WHERE id_project = %d",
					idProject);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestSuite ts = new TestSuite();
				ts.setId(resultSet.getInt("id"));
				ts.setIdProject(resultSet.getInt("id_project"));
				ts.setName(resultSet.getString("name"));
				ts.setTime(resultSet.getString("time"));
				ts.setDescription(resultSet.getString("description"));
				ts.setCode(resultSet.getInt("code"));
				ts.setCheckOut(resultSet.getString("checkout"));
				listTestSuite.add(ts);
			}
			return listTestSuite;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}
	
	public ArrayList<TestSuite> getAllTestSuite(String checkOut) {
		ArrayList<TestSuite> listTestSuite = new ArrayList<TestSuite>();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testsuite WHERE checkout = '%s'",
					checkOut);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestSuite ts = new TestSuite();
				ts.setId(resultSet.getInt("id"));
				ts.setIdProject(resultSet.getInt("id_project"));
				ts.setName(resultSet.getString("name"));
				ts.setTime(resultSet.getString("time"));
				ts.setDescription(resultSet.getString("description"));
				ts.setCode(resultSet.getInt("code"));
				ts.setCheckOut(resultSet.getString("checkout"));
				listTestSuite.add(ts);
			}
			return listTestSuite;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	// test case
	public boolean addTestCase(TestCase tc) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("INSERT INTO tbl_testcase(id_testmodule,id_testscript,name,time,content) VALUES (%d,%d,'%s',NOW(),'%s')",
							tc.getIdTestModule(), tc.getTS().getId(),
							tc.getName(), tc.getContent());
			statement.executeUpdate(query);
			query = String.format("SELECT MAX(id) as max FROM tbl_testcase");
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				tc.setId(resultSet.getInt("max"));
			}
			return true;
		} catch (Exception e) {
			//System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean updateTestCase(TestCase tc) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testcase SET id_testmodule = %d,name = '%s',time = NOW(), content = '%s', code = code + 1 WHERE id = %d",
							tc.getIdTestModule(), tc.getName(),
							tc.getContent(), tc.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}
	
	public boolean checkOutTestCase(TestCase tc) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testcase SET checkout = '%s' WHERE id = %d",
							tc.getCheckOut(), tc.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}
	
	public void releaseAllCheckOutTestCase(String checkOut) {
		try {
			ArrayList<TestCase> listTestCase = this.getAllTestCase(checkOut);
			if(listTestCase != null){
				Statement statement = con.createStatement();
				for (TestCase tc : listTestCase) {
					String query = String
							.format("UPDATE tbl_testcase SET checkout = '' WHERE id = %d",
									tc.getId());
					statement.executeUpdate(query);
				}
			}
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
	}
	
	public boolean renameTestCase(TestCase tc) {
		try {
			Statement statement = con.createStatement();
			tc.getTS().setName(tc.getName());
			renameTestScript(tc.getTS());
			String query = String.format(
					"UPDATE tbl_testcase SET name = '%s' WHERE id = %d",
					tc.getName(), tc.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean moveTestCase(TestCase tc) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"UPDATE tbl_testcase SET id_testmodule = %d WHERE id = %d",
					tc.getIdTestModule(), tc.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			//System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteTestCase(TestCase tc) {
		try {
			deleteTestScript(tc.getTS().getId()); // test script link test case			
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public TestCase getTestCase(int id) {
		TestCase tc = new TestCase();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testcase WHERE id = %d", id);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				tc.setId(resultSet.getInt("id"));
				tc.setIdTestModule(resultSet.getInt("id_testmodule"));
				tc.getTS().setId(resultSet.getInt("id_testscript"));
				tc.setName(resultSet.getString("name"));
				tc.setTime(resultSet.getString("time"));
				tc.setContent(resultSet.getString("content"));
				tc.setCode(resultSet.getInt("code"));
				tc.setCheckOut(resultSet.getString("checkout"));
			}
			return tc;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return null;
		}		
	}

	public ArrayList<TestCase> getAllTestCase(String checkOut) {
		ArrayList<TestCase> listTestCase = new ArrayList<TestCase>();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testcase WHERE checkout = '%s'",
					checkOut);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestCase tc = new TestCase();
				tc.setId(resultSet.getInt("id"));
				tc.setIdTestModule(resultSet.getInt("id_testmodule"));
				tc.getTS().setId(resultSet.getInt("id_testscript"));
				tc.setName(resultSet.getString("name"));
				tc.setTime(resultSet.getString("time"));
				tc.setContent(resultSet.getString("content"));
				tc.setCode(resultSet.getInt("code"));
				tc.setCheckOut(resultSet.getString("checkout"));
				listTestCase.add(tc);
			}
			return listTestCase;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return null;
		}		
	}
	
	public ArrayList<TestCase> getAllTestCaseInTestModule(int idTestModule) {
		ArrayList<TestCase> listTestCase = new ArrayList<TestCase>();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testcase WHERE id_testmodule = %d",
					idTestModule);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestCase tc = new TestCase();
				tc.setId(resultSet.getInt("id"));
				tc.setIdTestModule(resultSet.getInt("id_testmodule"));
				tc.getTS().setId(resultSet.getInt("id_testscript"));
				tc.setName(resultSet.getString("name"));
				tc.setTime(resultSet.getString("time"));
				tc.setContent(resultSet.getString("content"));
				tc.setCode(resultSet.getInt("code"));
				tc.setCheckOut(resultSet.getString("checkout"));
				listTestCase.add(tc);
			}
			return listTestCase;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	public ArrayList<TestCase> getAllTestCaseInTestSuite(int idTestSuite) {
		ArrayList<TestCase> listTestCase = new ArrayList<TestCase>();
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("SELECT * FROM tbl_testcase,tbl_run WHERE id_testsuite = %d AND id_testcase = tbl_testcase.id ORDER BY tbl_run.order ASC",
							idTestSuite);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestCase tc = new TestCase();
				tc.setId(resultSet.getInt("id"));
				tc.setIdTestModule(resultSet.getInt("id_testmodule"));
				tc.getTS().setId(resultSet.getInt("id_testscript"));
				tc.setName(resultSet.getString("name"));
				tc.setTime(resultSet.getString("time"));
				tc.setContent(resultSet.getString("content"));
				tc.setCode(resultSet.getInt("code"));
				tc.setOrder(resultSet.getInt("order"));
				tc.setCheckOut(resultSet.getString("checkout"));
				listTestCase.add(tc);
			}
			return listTestCase;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	// test script
	public boolean addTestScript(TestScript ts) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("INSERT INTO tbl_testscript(id_project,type,name,time,content) VALUES (%d,'script','%s',NOW(),'%s')",
							ts.getIdProject(), ts.getName(), ts.getContent());
			statement.executeUpdate(query);
			query = String.format("SELECT MAX(id) as max FROM tbl_testscript");
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				ts.setId(resultSet.getInt("max"));
			}
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean updateTestScript(TestScript ts) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testscript SET id_project = %d,type = 'script',name = '%s',time = NOW(),content = '%s',code = code + 1 WHERE id = %d",
							ts.getIdProject(), ts.getName(), ts.getContent(),
							ts.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean checkOutTestScript(TestScript ts) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testscript SET checkout = '%s' WHERE id = %d",
							ts.getCheckOut(), ts.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}
	
	public void releaseAllCheckOutTestScript(String checkOut) {
		try {
			ArrayList<TestScript> listTestScript = this.getAllTestScript(checkOut);
			if(listTestScript != null){
				Statement statement = con.createStatement();
				for (TestScript ts : listTestScript) {
					String query = String
							.format("UPDATE tbl_testscript SET checkout = '' WHERE id = %d",
									ts.getId());
					statement.executeUpdate(query);
				}
			}
		} catch (Exception e) {
			//System.err.println("Error with connection: " + e);
		}
	}

	public boolean renameTestScript(TestScript ts) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"UPDATE tbl_testscript SET name = '%s' WHERE id = %d",
					ts.getName(), ts.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteTestScript(TestScript ts) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_testscript WHERE id = %d", ts.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteTestScript(int id) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_testscript WHERE id = %d", id);
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public TestScript getTestScript(int id) {
		TestScript ts = new TestScript();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testscript WHERE id = %d", id);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				ts.setId(resultSet.getInt("id"));
				ts.setIdProject(resultSet.getInt("id_project"));
				ts.setName(resultSet.getString("name"));
				ts.setTime(resultSet.getString("time"));
				ts.setContent(resultSet.getString("content"));
				ts.setCode(resultSet.getInt("code"));
				ts.setCheckOut(resultSet.getString("checkout"));
			}
			return ts;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	public ArrayList<TestScript> getAllTestScript(String checkOut) {
		ArrayList<TestScript> listTestScript = new ArrayList<TestScript>();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testscript WHERE checkout = '%s' AND type = 'script'",
					checkOut);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestScript ts = new TestScript();
				ts.setId(resultSet.getInt("id"));
				ts.setIdProject(resultSet.getInt("id_project"));
				ts.setName(resultSet.getString("name"));
				ts.setTime(resultSet.getString("time"));
				ts.setContent(resultSet.getString("content"));
				ts.setCode(resultSet.getInt("code"));
				ts.setCheckOut(resultSet.getString("checkout"));
				listTestScript.add(ts);
			}
			return listTestScript;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}
	
	public ArrayList<TestScript> getAllTestScriptInProject(int idProject) {
		ArrayList<TestScript> listTestScript = new ArrayList<TestScript>();
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("SELECT * FROM tbl_testscript WHERE id_project = %d AND type = 'script'",
							idProject);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestScript ts = new TestScript();
				ts.setId(resultSet.getInt("id"));
				ts.setIdProject(resultSet.getInt("id_project"));
				ts.setName(resultSet.getString("name"));
				ts.setTime(resultSet.getString("time"));
				ts.setContent(resultSet.getString("content"));
				ts.setCode(resultSet.getInt("code"));
				ts.setCheckOut(resultSet.getString("checkout"));
				listTestScript.add(ts);
			}
			return listTestScript;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	// function
	public boolean addFunction(Function f) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("INSERT INTO tbl_testscript(id_project,type,name,time,content) VALUES (%d,'function','%s',NOW(),'%s')",
							f.getIdProject(), f.getName(), f.getContent());
			statement.executeUpdate(query);
			query = String
					.format("SELECT MAX(id) as max FROM tbl_testscript WHERE type = 'function'");
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				f.setId(resultSet.getInt("max"));
			}
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean updateFunction(Function f) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testscript SET id_project = %d,type = 'function',name = '%s',time = NOW(),content = '%s',code = code + 1 WHERE id = %d",
							f.getIdProject(), f.getName(), f.getContent(),
							f.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean checkOutFunction(Function f) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testscript SET checkout = '%s' WHERE id = %d",
							f.getCheckOut(), f.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}
	
	public void releaseAllCheckOutFunction(String checkOut) {
		try {
			ArrayList<Function> listFunction = this.getAllFunction(checkOut);
			if(listFunction != null){
				Statement statement = con.createStatement();
				for (Function f : listFunction) {
					String query = String
							.format("UPDATE tbl_testscript SET checkout = '' WHERE id = %d",
									f.getId());
					statement.executeUpdate(query);
				}
			}
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
	}

	public boolean renameFunction(Function f) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"UPDATE tbl_testscript SET name = '%s' WHERE id = %d",
					f.getName(), f.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteFunction(Function f) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_testscript WHERE id = %d", f.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteFunction(int idProject) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_testscript WHERE id_project = %d",
					idProject);
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public Function getFunction(int id) {
		Function f = new Function();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testscript WHERE id = %d", id);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				f.setId(resultSet.getInt("id"));
				f.setIdProject(resultSet.getInt("id_project"));
				f.setName(resultSet.getString("name"));
				f.setTime(resultSet.getString("time"));
				f.setContent(resultSet.getString("content"));
				f.setCode(resultSet.getInt("code"));
				f.setCheckOut(resultSet.getString("checkout"));
			}
			return f;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}
	
	public Function getFunction(String name,int idProject) {
		Function f = new Function();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testscript WHERE id_project = %d AND name = '%s'", idProject,name);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				f.setId(resultSet.getInt("id"));
				f.setIdProject(resultSet.getInt("id_project"));
				f.setName(resultSet.getString("name"));
				f.setTime(resultSet.getString("time"));
				f.setContent(resultSet.getString("content"));
				f.setCode(resultSet.getInt("code"));
				f.setCheckOut(resultSet.getString("checkout"));
				break;
			}
			return f;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	public ArrayList<Function> getAllFunction(String checkOut) {
		ArrayList<Function> listFunction = new ArrayList<Function>();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testscript WHERE checkout = '%s' AND type = 'function'",
					checkOut);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				Function f = new Function();
				f.setId(resultSet.getInt("id"));
				f.setIdProject(resultSet.getInt("id_project"));
				f.setName(resultSet.getString("name"));
				f.setTime(resultSet.getString("time"));
				f.setContent(resultSet.getString("content"));
				f.setCode(resultSet.getInt("code"));
				f.setCheckOut(resultSet.getString("checkout"));
				listFunction.add(f);
			}
			return listFunction;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}
	
	public ArrayList<Function> getAllFunctionInProject(int idProject) {
		ArrayList<Function> listFunction = new ArrayList<Function>();
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("SELECT * FROM tbl_testscript WHERE id_project = %d AND type = 'function'",
							idProject);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				Function f = new Function();
				f.setId(resultSet.getInt("id"));
				f.setIdProject(resultSet.getInt("id_project"));
				f.setName(resultSet.getString("name"));
				f.setTime(resultSet.getString("time"));
				f.setContent(resultSet.getString("content"));
				f.setCode(resultSet.getInt("code"));
				f.setCheckOut(resultSet.getString("checkout"));
				listFunction.add(f);
			}
			return listFunction;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	// test data
	public boolean addTestData(TestData td) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("INSERT INTO tbl_testdata(id_project,name,time,content,code) VALUES (%d,'%s',NOW(),'%s',0)",
							td.getIdProject(), td.getName(), td.getContent());
			statement.executeUpdate(query);
			query = String.format("SELECT MAX(id) as max FROM tbl_testdata");
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				td.setId(resultSet.getInt("max"));
			}
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean updateTestData(TestData td) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testdata SET id_project = %d,name = '%s',time = NOW(), content = '%s', code = code + 1 WHERE id = %d",
							td.getIdProject(), td.getName(), td.getContent(),
							td.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean checkOutTestData(TestData td) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testdata SET checkout = '%s' WHERE id = %d",
							td.getCheckOut(), td.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}
	
	public void releaseAllCheckOutTestData(String checkOut) {
		try {
			ArrayList<TestData> listTestData = this.getAllTestData(checkOut);
			if(listTestData != null){
				Statement statement = con.createStatement();
				for (TestData td : listTestData) {
					String query = String
							.format("UPDATE tbl_testdata SET checkout = '' WHERE id = %d",
									td.getId());
					statement.executeUpdate(query);
				}
			}
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
	}

	public boolean renameTestData(TestData td) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"UPDATE tbl_testdata SET name = '%s' WHERE id = %d",
					td.getName(), td.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteTestData(TestData td) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_testdata WHERE id = %d", td.getId());
			statement.executeUpdate(query);
			deleteTestResult(td.getId());
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public TestData getTestData(int id) {
		TestData td = new TestData();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testdata WHERE id = %d", id);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				td.setId(resultSet.getInt("id"));
				td.setIdProject(resultSet.getInt("id_project"));
				td.setName(resultSet.getString("name"));
				td.setTime(resultSet.getString("time"));
				td.setContent(resultSet.getString("content"));
				td.setCode(resultSet.getInt("code"));
				td.setCheckOut(resultSet.getString("checkout"));
			}
			return td;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	public ArrayList<TestData> getAllTestData(String checkOut) {
		ArrayList<TestData> listTestData = new ArrayList<TestData>();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testdata WHERE checkout = '%s'",
					checkOut);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestData td = new TestData();
				td.setId(resultSet.getInt("id"));
				td.setIdProject(resultSet.getInt("id_project"));
				td.setName(resultSet.getString("name"));
				td.setTime(resultSet.getString("time"));
				td.setContent(resultSet.getString("content"));
				td.setCode(resultSet.getInt("code"));
				td.setCheckOut(resultSet.getString("checkout"));
				listTestData.add(td);
			}
			return listTestData;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}
	
	public ArrayList<TestData> getAllTestData(int idProject) {
		ArrayList<TestData> listTestData = new ArrayList<TestData>();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testdata WHERE id_project = %d",
					idProject);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestData td = new TestData();
				td.setId(resultSet.getInt("id"));
				td.setIdProject(resultSet.getInt("id_project"));
				td.setName(resultSet.getString("name"));
				td.setTime(resultSet.getString("time"));
				td.setContent(resultSet.getString("content"));
				td.setCode(resultSet.getInt("code"));
				td.setCheckOut(resultSet.getString("checkout"));
				listTestData.add(td);
			}
			return listTestData;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	// test result
	public boolean addTestResult(TestResult tr) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("INSERT INTO tbl_testresult(id_testsuite,id_testdata,name,time,fails,content) VALUES (%d,%d,'%s',NOW(),%d,'%s')",
							tr.getIdTestSuite(), tr.getIdTestData(),tr.getName(),
							tr.getFails(), tr.getContent());
			statement.executeUpdate(query);
			query = String.format("SELECT MAX(id) as max FROM tbl_testresult");
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				tr.setId(resultSet.getInt("max"));
			}
			return true;
		} catch (Exception e) {
			//System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean updateTestResult(TestResult tr) {
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("UPDATE tbl_testresult SET id_testsuite = %d,id_testdata = %d,name = '%s',time = NOW(), fails = %d, content = '%s' WHERE id = %d",
							tr.getIdTestSuite(), tr.getIdTestData(),tr.getName(),
							tr.getFails(), tr.getContent(), tr.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteTestResult(TestResult tr) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_testresult WHERE id = %d", tr.getId());
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public boolean deleteTestResult(int idTestData) {
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"DELETE FROM tbl_testresult WHERE id_testdata = %d",
					idTestData);
			statement.executeUpdate(query);
			return true;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
			return false;
		}
	}

	public TestResult getTestResult(int id) {
		TestResult tr = new TestResult();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testresult WHERE id = %d", id);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				tr.setId(resultSet.getInt("id"));
				tr.setIdTestSuite(resultSet.getInt("id_testsuite"));
				tr.setIdTestData(resultSet.getInt("id_testdata"));
				tr.setName(resultSet.getString("name"));
				tr.setTime(resultSet.getString("time"));
				tr.setFails(resultSet.getInt("fails"));
				tr.setContent(resultSet.getString("content"));
			}
			return tr;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	public ArrayList<TestResult> getAllTestResult(int idTestSuite) {
		ArrayList<TestResult> listTestResult = new ArrayList<TestResult>();
		try {
			Statement statement = con.createStatement();
			String query = String.format(
					"SELECT * FROM tbl_testresult WHERE id_testsuite = %d",
					idTestSuite);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestResult tr = new TestResult();
				tr.setId(resultSet.getInt("id"));
				tr.setIdTestSuite(resultSet.getInt("id_testsuite"));
				tr.setIdTestData(resultSet.getInt("id_testdata"));
				tr.setName(resultSet.getString("name"));
				tr.setTime(resultSet.getString("time"));
				tr.setFails(resultSet.getInt("fails"));
				tr.setContent(resultSet.getString("content"));
				listTestResult.add(tr);
			}
			return listTestResult;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}

	public ArrayList<TestResult> getAllTestResultInProject(int idProject) {
		ArrayList<TestResult> listTestResult = new ArrayList<TestResult>();
		try {
			Statement statement = con.createStatement();
			String query = String
					.format("SELECT * FROM tbl_testresult,tbl_testsuite WHERE id_testsuite = tbl_testsuite.id AND id_project = '%d'",
							idProject);
			ResultSet resultSet = statement.executeQuery(query);
			while (resultSet.next()) {
				TestResult tr = new TestResult();
				tr.setId(resultSet.getInt("id"));
				tr.setIdTestSuite(resultSet.getInt("id_testsuite"));
				tr.setIdTestData(resultSet.getInt("id_testdata"));
				tr.setName(resultSet.getString("name"));
				tr.setTime(resultSet.getString("time"));
				tr.setFails(resultSet.getInt("fails"));
				tr.setContent(resultSet.getString("content"));
				listTestResult.add(tr);
			}
			return listTestResult;
		} catch (Exception e) {
			// System.err.println("Error with connection: " + e);
		}
		return null;
	}
	// public static void main(String args[]) {
	// }
}
